package com.silky.common.service;


import com.silky.common.bean.DataSourceConfig;
import com.silky.common.bean.JdbcConfig;
import com.silky.common.bean.NodeConfig;
import com.silky.common.constant.IConstant;
import com.silky.common.utils.JdbcUtil;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 节点服务
 */
@Slf4j
public class NodeService {
    public SqlService getSqlService() {
        return sqlService;
    }

    private SqlService sqlService = new SqlService();
    private  DataSourceConfig dataSourceConfig;
    public NodeService(DataSourceConfig dataSourceConfig){
        this.dataSourceConfig = dataSourceConfig;
    }
    /**
     * 根据joinResultFields提取留下字段
     * @param bigMap
     * @param smallMap
     * @param testMap
     * @param bigDataSetName
     * @param smallDataSetName
     * @return
     */
    public Map<String, Object> mergeMap(Map<String, Object> bigMap, Map<String, Object> smallMap, Map<String,Object> testMap, String bigDataSetName, String smallDataSetName) throws Exception {
        Map<String,Object> mergeMap = new HashMap<>();
            //循环替换带${}
        for(String key:testMap.keySet()){
            String value = (String) testMap.get(key);
            Object value1 = getMapValue(bigMap, bigDataSetName,   value);
            if(value1!=null) {
                mergeMap.put(key,value1);
            }else{
                value1 = getMapValue(smallMap, smallDataSetName,   value);
                if(value1!=null){
                    mergeMap.put(key,value1);
                }else{
                    mergeMap.put(key,value1);
                }
            }
        }
        return mergeMap;
    }

    /**
     * 获取Map指定key
     * @param map
     * @param dataSetName
     * @param value
     * @return
     */
    public Object getMapValue(Map<String, Object> map, String dataSetName,  String value) throws Exception {
        String testPrexfix = "${"+ dataSetName +".";
        if(value.startsWith(testPrexfix)){
            int startPos = value.indexOf(testPrexfix);
            int epos = value.indexOf("}");
            if(epos==-1)
                throw new Exception("无效的表达式:"+value);
            String mapKey = value.substring(startPos+testPrexfix.length(),epos);
            return map.get(mapKey);
        }
        return null;
    }

    /**
     * 以关联的key构造Map
     * @param joinColumns
     * @param bigDataSet
     * @param bigDataSetName
     * @return
     */
    public Map<String, List<Map<String, Object>>> getMap(String joinColumns, List<Map<String, Object>> bigDataSet, String bigDataSetName) {
        List<String> fieldNames = getJoinColumnNames(joinColumns, bigDataSetName);
        Map<String, List<Map<String, Object>>> map = new HashMap<>();
        for(Map<String, Object> tableMap:bigDataSet){
            String key = makeKey(tableMap,fieldNames);
            if(!map.containsKey(key)) {
                List<Map<String, Object>> list = new ArrayList<>();
                list.add(tableMap);
                map.put(key, list);
            }else{
                System.out.println("one key have more than one value!");
                List<Map<String, Object>> list   = map.get(key);
                list.add(tableMap);
                map.put(key, list);
            }
        }
        return map;
    }
    /**
     * 构造key
     * @param map
     * @param joinColumns
     * @return
     */
    public String makeKey(Map<String, Object> map, List<String> joinColumns) {

        StringBuilder sb = new StringBuilder();
        for(String columnName:joinColumns){
            sb.append(map.get(columnName)).append("_");
        }
        return sb.toString();
    }
    public List<String> getJoinColumnNames(String joinColumns, String bigDataSetName) {
        String joinParis[] = joinColumns.split(",");
        List<String> joinDataSetNames = new ArrayList<>();
        String joinDataSetName;
        String lowerCaseBigDataSetName = bigDataSetName.toLowerCase();
        List<String> keys = new ArrayList<>();
        for(String joinPair:joinParis) {
            String[] joinDataSetName1 = joinPair.split("=");
            if(joinDataSetName1[0].toLowerCase().startsWith(lowerCaseBigDataSetName+".")){
                keys.add(joinDataSetName1[0].toLowerCase().replaceAll(lowerCaseBigDataSetName+".",""));
            }else if(joinDataSetName1[1].toLowerCase().startsWith(lowerCaseBigDataSetName+".")) {
                keys.add(joinDataSetName1[1].toLowerCase().replaceAll(lowerCaseBigDataSetName + ".", ""));
            }
        }
        return keys;
    }

    /**
     * 获取关联的别名数据集合
     * @param nodeConfig
     * @return
     */
    public List<String> getJoinDataSetNames(NodeConfig nodeConfig) {
        String joinParis[] = nodeConfig.getJoinColumns().split(",");
        List<String> joinDataSetNames = new ArrayList<>();
        String joinDataSetName;
        for(String joinPair:joinParis){
            String[] joinDataSetName1 = joinPair.split("=");
            for(String joinDataSetName2:joinDataSetName1) {
                int startPos = joinDataSetName2.indexOf(".");
                if (startPos >= 0) {
                    joinDataSetName = joinDataSetName2.substring(0, startPos);
                    if (joinDataSetName != null) {
                        joinDataSetName = joinDataSetName.toLowerCase().trim();
                        if (!joinDataSetNames.contains(joinDataSetName)) {
                            joinDataSetNames.add(joinDataSetName);
                        }
                    }
                }
            }
        }
        return joinDataSetNames;
    }

    /**
     * 查询某页数据
     * @param nodeConfig
     * @param offset
     * @param connection
     * @return
     */
    public List<Map<String, Object>> getTableData(NodeConfig nodeConfig, Long offset, Connection connection) throws Exception {
        if(nodeConfig.getType().equals(IConstant.NODE_TYPE_JOIN)||nodeConfig.getType().equals(IConstant.NODE_TYPE_SINGLE)) {
            String sqlTemplate = nodeConfig.getOrginSql()+nodeConfig.getPageSqlTemplate();
            String sql = sqlTemplate.replace("${offset}", offset.toString());
            sql = sql.replace("${limit}", nodeConfig.getBatchSize() + "");
            return sqlService.search(connection, sql, null);
        }else{
            return null;
        }
    }

    /**
     * 关闭数据库连接
     * @param connection
     */
    public void closeConnection(Connection connection)  {
        if(connection!=null){
            try {
                JdbcUtil.closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
                log.error("Close db connection fail", e);
            }
        }
    }

    /**
     * 关闭数据库连接
     * @param nodeConfig
     */
    private void closeConnection(NodeConfig nodeConfig)  {
        if(nodeConfig.getDbConnection()!=null){
            try {
                JdbcUtil.closeConnection(nodeConfig.getDbConnection());
            } catch (SQLException e) {
                e.printStackTrace();
                log.error("Close db connection fail", e);
            }
        }
    }

    /**
     * 拆分totalCount为偏移量数组
     * @param totalCount
     * @param batchSize
     * @return
     */
    public List<Long> splitOffset(long totalCount, int batchSize) {
        List<Long> offsetList = new ArrayList<>();
        long offset = 0;
        while (offset <= totalCount) {
            offsetList.add(offset);
            offset += batchSize;
        }
        return offsetList;
    }
    public long getCount(NodeConfig nodeConfig) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 加载JDBC驱动
            //在dataSourceConfig查询数据源
            JdbcConfig jdbcConfig = getJdbcConfig(nodeConfig.getDataSourceName());
            conn = JdbcUtil.getConnection(jdbcConfig);

            // 创建Statement
            stmt = conn.createStatement();
            // 执行SQL查询
            String sql = "SELECT COUNT(*) FROM (" + nodeConfig.getOrginSql()+") ttt1";
            rs = stmt.executeQuery(sql);

            // 处理结果集
            if (rs.next()) {
                long count = rs.getLong(1);
                return count;
            }
            return 0;
        } catch (Exception e) {
            log.error("Search big table fail", e);
            throw e;
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                JdbcUtil.closeConnection(conn);
            } catch (Exception e) {
                log.error("Search big table fail", e);
            }
        }
    }

    private JdbcConfig getJdbcConfig(String dataSourceName) {
        JdbcConfig jdbcConfig = findJdbcConfig(dataSourceConfig.getJdbcConfigs(), dataSourceName);
        if (jdbcConfig == null) {
            throw new RuntimeException("No found datasource config:" + dataSourceName);
        }
        return jdbcConfig;
    }

    /**
     * 根据数据源名称查找对应
     * @param jdbcConfigs
     * @param dataSourceName
     * @return
     */
    private JdbcConfig findJdbcConfig(List<JdbcConfig> jdbcConfigs, String dataSourceName) {
        if(jdbcConfigs==null||jdbcConfigs.isEmpty()){
            return null;
        }

        for(JdbcConfig config: jdbcConfigs){
            if(config.getDataSourceName().equals(dataSourceName)){
                return config;
            }
        }
        return null;
    }
    /**
     * 根据数据源名称获取连接
     * @param dataSourceName
     * @return
     */
    public Connection getConnectionByDataSourceName(String dataSourceName) throws Exception {
        JdbcConfig jdbcConfig = getJdbcConfig(dataSourceName);
        if(jdbcConfig==null)
            throw new Exception("No found datasource " + dataSourceName + "config！");

        return JdbcUtil.getConnection(jdbcConfig);
    }
    /**
     * 获取更新语句的数据集别名列表
     * @param updateSql
     * @return
     */
    public List<String> getUpdateSqlDataSetNames(String updateSql) {
        List<String> retKeys = getVarKeys(updateSql);
        List<String> retDataSetNames = new ArrayList<>();

        for (String key : retKeys) {
            String dataSetName = key.split("\\.")[0];
            if (!retDataSetNames.contains(dataSetName)) {
                retDataSetNames.add(dataSetName);
            }
        }
        return retDataSetNames;
    }
    /**
     * 获取sql变量key列表
     *
     * @param orgSql
     * @return
     */
    public List<String> getVarKeys(String orgSql) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(orgSql);
        List<String> replaceGroups = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        List<String> retKeys = new ArrayList<>();
        while (m.find()) {
            m.appendReplacement(sb, "?");
            if (m.groupCount() >= 3) {
                //System.out.println(m.group(2));
                retKeys.add(m.group(2));
            }
        }
        return retKeys;
    }
}
